-- 基于《数据库系统教程(第二版)》何玉洁 主编

-- 第六章实验上机训练 by Charlie

-- 触发器，存储过程

-- 创建满足如下要求的后触发型触发器

-- 1. 限制学生的考试成绩必须在0-100之间
create trigger tri1 on sc after insert, update
as
    if exists(select sno from inserted where grade not between 0 and 100)
    rollback;

-- 2. 限制不能删除成绩不及格的考试记录
create trigger tri2 on sc after delete
as
    if exists(select sno from deleted where grade < 60)
    rollback;

-- 3. 限制每个学期开设的课程总学分不能超过20
create trigger tri3 on course after insert, update
as
    if exists(select sum(credit) from course group by semester having sum(credit) > 20)
    rollback;

-- 4. 限制每个学生每学期选的课程不能超过5门
create trigger tri4 on sc after insert
as
    if exists(select count(t2.cno)
                from sc t1 join course t2 on t1.cno = t2.cno
                group by t1.sno, t2.semester
                having count(t2.cno) >  5)
    rollback;

-- 创建满足如下要求的存储过程

-- 5. 查询每个学生的修课总学分，要求列出学生学号及总学分
create proc p1
as begin
    select t1.sno, sum(t2.credit)
    from sc t1 join course t2 on t1.cno = t2.cno
    group by t1.sno;
end

-- 6. 查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在的系作为输入参数，执行此存储过程，并分别指定一些不同的输入参数值
create proc p2
@sdept char(20)
as begin
    select t2.sno, t2.sname, t3.cno, t3.cname, t3.credit
    from sc t1 join student t2 on t1.sno = t2.sno and t2.sdept = @sdept
               join course t3 on t1.cno = t3.cno 
end

-- 7. 查询指定系的男生人数，其中系为输入参数，人数为输出参数
create proc p3
@sdept char(20),
@count int output
as begin
    select count(sno)
    from student
    where sdept = @sdept and ssex = '男';
end

declare @count int, @sdept char(20)
exec p3 @sdept = '计算机系', @count output
print @count;

-- 8. 删除指定学生的修课记录，其中学号为输入参数
create proc p4
@sno char(20)
as begin
    delete from sc where sno = @sno
end

-- 9. 修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期
create proc p5
@cno char(20),
@semester int 
as begin
    update course set semester = @semester where cno = @cno
end